import 'dart:io';

import 'package:flutter/foundation.dart';
//import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:sqflite_common_ffi_web/sqflite_ffi_web.dart';

import '../models/sync_operation.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static Database? _database;

  factory DatabaseHelper() {
    return _instance;
  }

  DatabaseHelper._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'app_database.db');
    if (kIsWeb) {
      // Change default factory on the web
      databaseFactory = databaseFactoryFfiWeb;
      path = 'my_web_web.db';
    }
    if (Platform.isWindows || Platform.isLinux) {
      // Initialize FFI
      sqfliteFfiInit();
      databaseFactory = databaseFactoryFfi;
    }
    return await openDatabase(
      path,
      version: 2,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  void _onCreate(Database db, int version) async {
    // إنشاء الجداول عند إنشاء قاعدة البيانات
    await db.execute('''
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER UNIQUE,
        username TEXT,
        full_name TEXT,
        email TEXT,
        phone TEXT,
        password TEXT,
        barcode TEXT,
        image TEXT,
        level INTEGER,
        evaluation INTEGER,
        active INTEGER,
        public INTEGER,
        type INTEGER,
        description TEXT,
        power INTEGER,
        agility INTEGER,
        birthdate TEXT,
        commitment INTEGER,
        rate INTEGER,
        exercise_home INTEGER,
        gender TEXT,
        is_deleted INTEGER,
        create_at TEXT,
        update_at TEXT,
        created_by INTEGER
      )
    ''');

    await db.execute('''
      CREATE TABLE subscriptions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        subscription_id INTEGER UNIQUE,
        user_id INTEGER,
        start_date TEXT,
        end_date TEXT,
        exercise_id INTEGER,
        created_by INTEGER,
        create_at TEXT,
        update_at TEXT,
        FOREIGN KEY (user_id) REFERENCES users(user_id)
      )
    ''');

    await db.execute('''
      CREATE TABLE attendances (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        attendance_id INTEGER UNIQUE,
        user_id INTEGER,
        exercise_id INTEGER,
        subscription_id INTEGER,
        datetime TEXT,
        created_by INTEGER,
        create_at TEXT,
        update_at TEXT,
        FOREIGN KEY (user_id) REFERENCES users(user_id),
        FOREIGN KEY (subscription_id) REFERENCES subscriptions(subscription_id)
      )
    ''');
    // إنشاء جدول sync_operations
    await db.execute('''
      CREATE TABLE sync_operations (
        operation_id INTEGER PRIMARY KEY AUTOINCREMENT,
        endpoint TEXT,
        method TEXT,
        payload TEXT,
        status TEXT,
        error_message TEXT,
        created_at TEXT,
        updated_at TEXT
      )
    ''');
  }
  void _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < newVersion) {
      await db.execute('''
        CREATE TABLE IF NOT EXISTS sync_operations (
          operation_id INTEGER PRIMARY KEY AUTOINCREMENT,
          endpoint TEXT,
          method TEXT,
          payload TEXT,
          status TEXT,
          error_message TEXT,
          created_at TEXT,
          updated_at TEXT
        )
      ''');
      await db.execute('''
      ALTER TABLE users ADD public INTEGER
    ''');
    }
  }


  // عمليات CRUD لجداول المستخدمين (Users)
  static Future<int> insertUser(Map<String, dynamic> user) async {
    final db = await DatabaseHelper().database;
    return await db.insert('users', user);
  }

  static Future<List<Map<String , dynamic>>> getAllUsers() async {
    final db = await DatabaseHelper().database;
    return await db.query('users');
  }

  static Future<int> updateUser(int id, Map<String, dynamic> user) async {
    final db = await DatabaseHelper().database;
    return await db.update('users', user, where: 'user_id = ?', whereArgs: [id]);
  }

  static Future<int> deleteUser(int id) async {
    final db = await DatabaseHelper().database;
    return await db.delete('users', where: 'user_id = ?', whereArgs: [id]);
  }

  // عمليات CRUD لجداول الاشتراكات (Subscriptions)
  static Future<int> insertSubscription(Map<String, dynamic> subscription) async {
    final db = await DatabaseHelper().database;
    return await db.insert('subscriptions', subscription);
  }

  static Future<int> updateSubscription(int id, Map<String, dynamic> subscription) async {
    final db = await DatabaseHelper().database;
    return await db.update('subscriptions', subscription, where: 'subscription_id = ?', whereArgs: [id]);
  }

  static Future<int> deleteSubscription(int id) async {
    final db = await DatabaseHelper().database;
    return await db.delete('subscriptions', where: 'subscription_id = ?', whereArgs: [id]);
  }

  // عمليات CRUD لجداول الحضور (Attendances)
  static Future<int> insertAttendance(Map<String, dynamic> attendance) async {
    final db = await DatabaseHelper().database;
    return await db.insert('attendances', attendance);
  }

  static Future<int> updateAttendance(int id, Map<String, dynamic> attendance) async {
    final db = await DatabaseHelper().database;
    return await db.update('attendances', attendance, where: 'attendance_id = ?', whereArgs: [id]);
  }

  static Future<int> deleteAttendance(int id) async {
    final db = await DatabaseHelper().database;
    return await db.delete('attendances', where: 'attendance_id = ?', whereArgs: [id]);
  }


  // CRUD Operations for Sync Operations Table
  static Future<int> insertSyncOperation(SyncOperation operation) async {
    final db = await DatabaseHelper().database;
    return await db.insert('sync_operations', operation.toJson());
  }

  static Future<int> updateSyncOperation(int id, Map<String, dynamic> operation) async {
    final db = await DatabaseHelper().database;
    return await db.update('sync_operations', operation, where: 'operation_id = ?', whereArgs: [id]);
  }

  static Future<int> deleteSyncOperation(int id) async {
    final db = await DatabaseHelper().database;
    return await db.delete('sync_operations', where: 'operation_id = ?', whereArgs: [id]);
  }

  static Future<List<Map<String, dynamic>>> getPendingOperations() async {
    final db = await DatabaseHelper().database;
    return await db.query('sync_operations', where: 'status = ?', whereArgs: ['pending']);
  }
  static Future<List<Map<String, dynamic>>> getSyncedOperations() async {
    final db = await DatabaseHelper().database;
    return await db.query('sync_operations', where: 'status = ?', whereArgs: ['synced']);
  }
  static Future<List<Map<String, dynamic>>> getFailedOperations() async {
    final db = await DatabaseHelper().database;
    return await db.query('sync_operations', where: 'status = ?', whereArgs: ['failed']);
  }

  // تابع لتفريغ جميع البيانات من الجداول
  static Future<void> clearAllTables() async {
    final db = await DatabaseHelper().database;

    // تفريغ البيانات من كل جدول
    await db.execute('DELETE FROM users');
    await db.execute('DELETE FROM subscriptions');
    await db.execute('DELETE FROM attendances');
    await db.execute('DELETE FROM sync_operations');
  }
}
